﻿namespace Universe.MySqlTests
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Diagnostics;
    using System.Linq;
    using System.Text;

    using Dapper;

    using global::Dapper;

    using MySql.Data.MySqlClient;

    public static class MySqlExtentions 
    {
        public static object ExecuteScalarObject(this MySqlConnection con, string sql)
        {
            using (MySqlCommand cmd = new MySqlCommand(sql, con))
            {
                return cmd.ExecuteScalar();
            }
        }

        public enum VarScope
        {
            Global,
            Session
        };

        public static object GetVar(this MySqlConnection con, VarScope scope, string name)
        {
            string sql = "SHOW " + scope + " Variables like '" + name + "';";
            var row = ExecuteRow(con, sql);
            if (row == null) return null;
            object ret;
            row.TryGetValue("Value", out ret);
            return ret;
        }

        public static Dictionary<string, object> ExecuteRow(this MySqlConnection con, string sql)
        {
            using (MySqlCommand cmd = new MySqlCommand(sql, con))
            {
                using (var r = cmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (r.Read())
                    {
                        Dictionary<string, object> ret = new Dictionary<string, object>(StringComparer.InvariantCultureIgnoreCase);
                        for (int i = 0; i < r.FieldCount; i++)
                        {
                            ret[r.GetName(i)] = r.GetValue(i);
                        }

                        return ret;
                    }
                }
            }

            return null;
        }

        public static string GetVersionString(this MySqlConnection con)
        {
            string[] vars = new[] {"version", "version_compile_machine", "version_compile_os"};
            StringBuilder ver = new StringBuilder();
            foreach (var @var in vars)
            {
                object v = GetVar(con, VarScope.Global, var);
                if (v != null && v.ToString().Trim().Length > 0)
                    ver.Append(ver.Length == 0 ? "" : ", ").Append(v);

            }

            return ver.ToString();
        }

/*
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
*/
        public static void TraceCharset(MySqlConnection con)
        {
            var keys = @"client connection database filesystem results server system".Split(' ');

            List<string> vals = new List<string>();
            foreach (var key in keys)
            {
/*
                object val = GetVar(con, VarScope.Global, "character_set_" + key);
                vals.Add(Convert.ToString(val));
*/
            }

            Func<string, string> get = key =>
            {
                var y = Convert.ToString(GetVar(con, VarScope.Global, "character_set_" + key));
                return y == "" ? "n/a" : y;
            };

            Trace.WriteLine(string.Format("Charset                 {0,-15}     client: {1,-15}", "", get("client")));
            Trace.WriteLine(string.Format("~~~~~~~     connection: {0,-15}    results: {1,-15}", get("connection"), get("results")));
            Trace.WriteLine(string.Format("                server: {0,-15}   database: {1,-15}", get("server"), get("database")));
            Trace.WriteLine(string.Format("            filesystem: {0,-15}     system: {1,-15}", get("filesystem"), get("system")));
            Trace.WriteLine("");

            // utf8mb4, utf16, and utf32 character sets were added in MySQL 5.5.3
            var chars = SqlMapper.Query<string>(con, "SHOW CHARACTER SET;").OrderBy(x => x).ToArray();
            Trace.WriteLine("Charsets(" + chars.Length + "): " + string.Join(", ", chars));


        }

        public static bool HasInnoDb(this MySqlConnection con, out string version)
        {
            var c = StringComparer.InvariantCultureIgnoreCase;
            var list = con.Query<ShowEngineRow>("SHOW ENGINES;");
            bool isOk = list.Any(x => c.Equals("InnoDB", x.Engine) && (c.Equals("YES", x.Support) || c.Equals("DEFAULT", x.Support)));
            version = null;
            if (isOk)
                version = (string) GetVar(con, VarScope.Global, "innodb_version");
            
            return isOk;
        }

        public static string DefaultEngine(this MySqlConnection con)
        {
            var ret = (string) GetVar(con, VarScope.Global, "default_storage_engine");
            if (ret == null)
                ret = (string)GetVar(con, VarScope.Global, "storage_engine");

            return ret;
        }

        class ShowEngineRow
        {
            public string Engine;
            
            // YES, NO, DEFAULT
            public string Support;
        }
        
        public static List<DatabaseInfo> GetDatabases(this MySqlConnection con)
        {
            var sql = @"
SELECT 
  table_schema             as ""Name"",
  sum(data_length)  / 1024 as ""Data"",
  sum(index_length) / 1024 as ""Index"",
  sum(data_free)    / 1024 as ""Free""
FROM 
  information_schema.TABLES
GROUP BY 
  table_schema;";

            var nonEmpty = SqlMapper.Query<DatabaseInfo>(con, sql).ToList();
            var names = SqlMapper.Query<string>(con, "Show Databases;");
            IEnumerable<string> empty = names.Where(name => !nonEmpty.Select(db => db.Name).Contains(name));
            var ret = new List<DatabaseInfo>(empty.Select(x => new DatabaseInfo() {Name = x}));
            ret.AddRange(nonEmpty);
            ret = ret.OrderBy(x => x.Name).ToList();
            return ret;
        }

        public static List<TableInfo> GetTables(this MySqlConnection con, string database = null)
        {
            var sql = @"
SELECT 
  table_schema as ""Database"",
  table_name as ""Table"",
  table_type as ""Type"",
  table_rows as ""Rows"",
  Engine,
  TABLE_COLLATION as ""Collation"",
  data_length   / 1024 as ""Data"",
  index_length  / 1024 as ""Index"",
  data_free     / 1024 as ""Free""
FROM 
  information_schema.TABLES
WHERE 
  (@db is null) or (table_schema=@db);
";
            return con.Query<TableInfo>(sql, new { db = database }).ToList();
        }

        public class DatabaseInfo
        {
            public virtual string Name { get; set; }
            public virtual long Data { get; set; }
            public virtual long Index { get; set; }
            public virtual long Free { get; set; }
        }

        public class TableInfo
        {
            public virtual string Database { get; set; }
            
            public virtual string Table { get; set; }

            // TABLE, VIEW, SYSTEM VIEW
            public virtual string Type { get; set; }

            // MEMORY MyISAM CSV PERFORMANCE_SCHEMA
            public virtual string Engine { get; set; }

            public virtual string Collation { get; set; }

            // InndoDB's rows are approx
            public virtual long Rows { get; set; }
            public virtual long Data { get; set; }
            public virtual long Index { get; set; }
            public virtual long Free { get; set; }
        }
    }
}